#!/usr/bin/env python
# coding=UTF-8
import MySQLdb
import MySQLdb.cursors
from DBUtils.PooledDB import PooledDB
import logging
import time

import config
from unicodedata import bidirectional

pool = PooledDB(MySQLdb, 20, 20, 0, 500, **{'host':config.DB_HOST, 'port':config.DB_PORT, 'user':config.DB_USER, 'passwd':config.DB_PWD, 'db': config.DB_NAME, 'charset':'utf8', 'cursorclass':MySQLdb.cursors.DictCursor})

MySQLdb.connections

class BaseDB(object):
    def __init__(self, conn=None, slave=False, bid=''):
        if conn:
            self.conn = conn
        else:
            self.conn = pool.connection()
        self.cur = self.conn.cursor()
        self.res = True

    def __del__(self):
        self.finish()
        
    def execute(self, sql, param=None):
        try:
            if 'select' not in sql.lower()[:7]:
                logging.info(sql)
            else:
                logging.debug(sql)
            if not param:
                return self.cur.execute(sql)
            else:
                return self.cur.execute(sql, param)
        except:
            self.res = False
            logging.error(u"DB error: %s" % sql, exc_info=True)
    
    def get(self, sql, param=None):
        self.execute(sql, param)
        return self.cur.fetchone()

    def query(self, sql, param=None):
        self.execute(sql, param)
        return self.cur.fetchall()

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()
        
    def finish(self):
        if self.res:
            self.conn.commit()
        else:
            self.conn.rollback()
        if self.cur:
            self.cur.close()
        if self.conn:
            self.conn.close()
      
class Game_DB(BaseDB):
    def get_room(self, rid, bid):
        sql = "select * from t_room where rid=%s and bid='%s'" % (rid, bid)
        logging.info(sql)
        return self.get(sql) 
    
    def room_player_cnt(self, rid, status=None):
        sql = "select count(1) as player from t_room_player where rid='%s'" % rid
        if status:
            sql += " and status=%s" % status
        else:
            sql += " and uid>0"
        return self.get(sql)
    
    def update_player_online(self, rid, uid, online):
        sql = "update t_room_player set online='%s' where rid=%s and uid=%s" % (online, rid, uid)
        return self.execute(sql)
    
    def update_player_ready(self, rid, uid):
        sql = "update t_room_player set status=1,online='y' where rid='%s' and uid='%s'" % (rid, uid)
        return self.execute(sql)
    
    def dismiss_apply(self, rid, dismiss_apply_time, uid):
        sql = "update t_room set dismiss_apply='%s',apply_user='%s' where rid=%s" % (dismiss_apply_time, uid, rid)
        return self.execute(sql)
    
    def dismiss_agree(self, rid, uid):
        sql = "update t_room set dismiss_agree=dismiss_agree+1,agree_user=concat(agree_user, ',%s') where rid=%s" % (uid, rid)
        return self.execute(sql)
    
    def dismiss_refuse(self, rid):
        sql = "update t_room set dismiss_apply='',dismiss_agree=0, apply_user='', agree_user='' where rid=%s" % rid
        return self.execute(sql)
    
    def dismiss_room(self, rid):
        sql = "update t_room set flag='n' where rid=%s" % rid
        return self.execute(sql)
    
    def deduct_card(self, bid, uid, card):
        sql = "update t_user set card=card-%s where bid='%s' and uid=%s" % (card, bid, uid)
        return self.execute(sql)
    
    def expire_room(self):
        sql = "select * from t_room where flag='y' and (dismiss_apply is not null and dismiss_apply!='') and dismiss_apply<now()"
        return self.query(sql)
    
    def player_stat(self, rid):
        sql = "select uid,sum(horse) as horse,sum(dark_bar) as dark_bar,sum(white_bar) as white_bar,sum(score) as score,count(if(is_win=1, 1, null)) as win_num, max(dark_bar) as max_prize, max(score) as max_score, count(if(is_win=0, 1, null)) as lose_num from t_room_log where rid='%s' group by uid order by score" % rid
        return self.query(sql)
    
    def game_over(self, rid, game_round):
        sql = "update t_room set game_round=%s where rid=%s" % (game_round, rid)
        return self.execute(sql)
    
    def update_player(self, rid, uid, score, status=0):
        sql = "update t_room_player set status=%s,score=score+%s where rid=%s and uid=%s" % (status, score, rid, uid)
        return self.execute(sql)
    
    def get_player(self, rid, uid):
        sql = "select * from t_room_player where rid=%s and uid=%s" % (rid, uid)
        return self.get(sql)
    
    def user_info(self, bid, uid):
        sql = "select * from t_user where bid='%s' and uid=%s" % (bid, uid)
        return self.get(sql)
    
    def game_rule(self, gid):
        sql = "select * from t_game_rule where gid=%s" % gid
        return self.query(sql)
    
    def room_rule(self, rid):
        sql = "select * from t_room_rule where rid=%s" % rid
        return self.query(sql)

    def update_club_online(self, uid, online):
        sql = "update t_club_member set online='%s' where uid=%s" % (online, uid)
        return self.execute(sql)
    
    def update_match_online(self, mid, uid, online):
        sql = "update t_match_member set online='%s' where mid=%s and uid=%s" % (online, mid, uid)
        return self.execute(sql)

    def match_info(self, bid, mid):
        sql = "select * from t_match where bid='%s' and mid=%s" % (bid, mid)
        return self.get(sql)
    
    def add_cron(self, bid, act, rid, uid, exp_time):
        sql = "insert into t_cron(bid, act,rid,uid,exp_time)values('%s','%s',%s,%s,'%s')" % (bid, act, rid, uid, exp_time)
        return self.execute(sql)
    
    def has_user_stat(self, bid, uid, game_type):
        sql = "select * from t_user_stat where bid='%s' and uid=%s and game_type='%s'" % (bid, uid, game_type)
        return self.get(sql)
    
    def add_user_stat(self, bid, uid, game_type, win_round, score):
        sql = "insert into t_user_stat(bid, uid, game_type, all_round, win_round, score)values('%s',%s,'%s',1,%s,%s)" % (bid, uid, game_type, win_round, score)
        return self.execute(sql)
    
    def update_user_stat(self, bid, uid, game_type, win_round):
        sql = "update t_user_stat set all_round=all_round+1,win_round=win_round+%s where bid='%s' and uid=%s and game_type='%s'" % (win_round, bid, uid, game_type)
        return self.execute(sql)
    
    def get_connserver(self, ip, port):
        sql = "select * from t_connserver where ip='%s' and port=%s" % (ip, port)
        return self.get(sql)
    
    def add_connserver(self, ip, port, client):
        sql = "insert into t_connserver(ip, port, client)values('%s',%s,%s)" % (ip, port, client)
        return self.execute(sql)
    
    def update_connserver(self, ip, port, client):
        sql = "update t_connserver set ctime=now(), client=%s where ip='%s' and port=%s" % (client, ip, port)
        return self.execute(sql)
    
    def phz_stat(self, rid):
        sql = "select uid,sum(dark_bar) as bind,sum(white_bar)as run,sum(score)as score,sum(is_win) as win_num, count(if(win_type='win', 1, null)) as win_cnt, count(if(is_put_user=1, 1, null)) as lose_cnt from t_room_log where rid='%s' group by uid order by score" % rid
        return self.query(sql)
    
    def add_room_log(self, mid, gid, rid, game_round, uid, score, white_bar, dark_bar, is_dealer, is_win, is_put_user, win_type):
        sql = "insert into t_room_log(mid, gid, rid, game_round, uid, score, white_bar, dark_bar, is_dealer, is_win, is_put_user, win_type)values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (mid, gid, rid, game_round, uid, score, white_bar, dark_bar, is_dealer, is_win, is_put_user, win_type)
        return self.execute(sql)
    
    def add_mj_room_log(self, mid, gid, rid, game_round, uid, score, white_bar, dark_bar, horse, is_dealer, is_win, is_put_user=0, win_type=''):
        sql = "insert into t_room_log(mid, gid, rid, game_round, uid, score, white_bar, dark_bar, horse, is_dealer, is_win, is_put_user, win_type)values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (mid, gid, rid, game_round, uid, score, white_bar, dark_bar, horse, is_dealer, is_win, is_put_user, win_type)
        return self.execute(sql)
    
    def query_room_players(self, rid, bid):
        sql = "select a.uid,a.head,a.name,a.sex,b.ip,b.status,b.online,b.seat,b.gps_lat,b.gps_lon,b.score from t_user a, t_room_player b where b.rid=%s and a.uid=b.uid and a.bid='%s'" % (rid, bid)
        return self.query(sql)
    
    def add_room_stat(self, mid, gid, rid, uid, score, is_winner='n'):
        sql = "select * from t_room_stat where rid='%s' and uid='%s'" % (rid, uid)
        row = self.get(sql)
        if row:
            return 1
        sql = "insert into t_room_stat(mid, gid, rid, uid, score,is_winner)values('%s','%s','%s','%s','%s','%s')" % (mid, gid, rid, uid, score, is_winner)
        return self.execute(sql)
    
    def save_game_over(self, rid, game_round, act, uid, msg):
        if act == "game_over":
            sql = "select * from t_game_over_msg where rid=%s and game_round=%s and act='%s' and uid='%s'" % (rid, game_round, act, uid)
            if self.get(sql):
                sql = "update t_game_over_msg set msg='%s' where rid=%s and game_round=%s and act='%s' and uid='%s'" % (msg, rid, game_round, act, uid)
                return self.execute(sql)
        else:
            sql = "select * from t_game_over_msg where rid=%s and act='%s' and uid='%s'" % (rid, act, uid)
            if self.get(sql):
                sql = "update t_game_over_msg set msg='%s' where rid=%s and act='%s' and uid='%s'" % (msg, rid, act, uid)
                return self.execute(sql)
        sql = "insert into t_game_over_msg(rid, game_round, act, uid, msg)values('%s','%s','%s','%s','%s')" % (rid, game_round, act, uid, msg)
        return self.execute(sql)
    
    def get_game_over_msg(self, rid, game_round, act, uid):
        sql = "select * from t_game_over_msg where rid=%s and act='%s' and uid=%s " % (rid, act, uid) 
        if act == 'game_over':
            sql += "and game_round=%s" % game_round
        return self.get(sql)
    
    def get_joined_match_room(self, mid, uid):
        sql = "select a.* from t_room a, t_room_player b where a.rid=b.rid and a.flag='y' and a.mid='%s' and b.uid='%s' order by mid desc limit 1" % (mid, uid)
        return self.get(sql)
    
    def match_member_info(self, mid, uid):
        sql = "select * from t_match_member where mid=%s and uid=%s" % (mid, uid)
        return self.get(sql)

    def add_dn_room_log(self, mid, gid, rid, game_round, member, win_score, bull_8, bull_9, bull_10, bomb, five_flower, five_small, is_dealer):
        sql = "insert into t_dn_room_log(mid, gid, rid, game_round, uid, score, bull_8, bull_9, bull_10, bomb, five_flower, five_small, is_dealer)values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" %(mid, gid, rid, game_round, member, win_score, bull_8, bull_9, bull_10, bomb, five_flower, five_small, is_dealer)
        return self.execute(sql)
    
    def player_stat_dn(self, rid):
        sql = "select uid, sum(bull_8) as bull_8,sum(bull_9) as bull_9,sum(bull_10)as bull_10,sum(bomb)as bomb,sum(five_flower) as five_flower, sum(five_small) as five_small, sum(score) as score from t_dn_room_log where rid='%s' group by uid order by score" % rid
        return self.query(sql)
    